from .models import LunchItemOrder, LunchItem, Student, Teacher

_customer_id_as_teacher_ = 100000000
_teacher_count_max_ = _customer_id_as_teacher_ - 1

common_sql = f"""
SELECT
  i.id AS item_id,
  i.name AS item,

  CASE
    WHEN m.name IS NOT NULL THEN m.id
    WHEN t.name IS NOT NULL THEN t.id
    ELSE {_teacher_count_max_}
  END AS mentor_id,
  CASE
    WHEN m.name IS NOT NULL THEN m.name
    WHEN t.name IS NOT NULL THEN t.name
    ELSE '-'
  END AS mentor,

  CASE
    WHEN s.name IS NOT NULL THEN s.id
    ELSE t.id + {_customer_id_as_teacher_}
  END AS customer_id,
  CASE
    WHEN s.name IS NOT NULL THEN s.name
    ELSE t.name
  END AS customer,

  sum(o.quantity) AS subtotal
FROM {LunchItemOrder._meta.db_table} o
LEFT JOIN {LunchItem._meta.db_table} i ON i.id = o.lunch_item_id
LEFT JOIN {Teacher._meta.db_table}   t ON t.id = o.teacher_id
LEFT JOIN {Student._meta.db_table}   s ON s.id = o.student_id
LEFT JOIN {Teacher._meta.db_table}   m ON m.id = s.teacher_id
GROUP BY item_id, item, mentor_id, mentor, customer_id, customer
"""

order_by_item_first = f"""
  ORDER BY item_id, mentor_id, customer_id
"""
order_by_item_last = f"""
  ORDER BY mentor_id, customer_id, item_id
"""

def generate_sql(orderByItem = True):
    if orderByItem:
      return f"""
          {common_sql}
          {order_by_item_first}
      """
    else:
      return f"""
          {common_sql}
          {order_by_item_last}
      """
